19c 新特性： Hint Usage Reports详解
老张拉呱：thomas zhang，甲骨文云平台事业部资深技术顾问，2008年加入甲骨文公司数据库咨询部门，10+年甲骨文解决方案咨询支持经验，资深系统工程师、Oracle OCM认证专家，具有丰富的Cloud /IT项目经验。目前主要负责甲骨文中国北方区（医院/卫生、交通、制造、教育、政府、证券、媒体、金融、零售等行业）客户的数据库、中间件、IaaS/PaaS、集成系统等相关技术解决方案咨询工作。
签名：我为人人，人人为我，三人行，必有我师。
新浪微博： http://weibo.com/tomszrp
Oracle Hints 是 SQL语句中的注释机制，用来传递指令给 Oracle 数据库优化器，告诉优化器按照我们的告诉它的方式 选择执行计划，除非存在某种条件阻止优化器这样做，典型的比如设置了 OPTIMIZER_IGNORE_HINTS、OPTIMIZER_IGNORE_PARALLEL_HINTS 参数或发生了查询转换甚至 Hint 冲突，那么可能会导致 Hint 失效。
最早在 Oracle Database 7 中就引入了 Hint，那时候当优化器生成执行计划时，用户几乎没有什么可以求助的资源。不像现在 Oracle 数据库中提供了丰富的优化工具，典型的比如SQL Tuning Advisor、SQL plan management以及SQL Performance Analyzer 等等，从而可以帮助我们解决优化器无法解决的性能问题。
Oracle 建议尽量使用这些辅助的调优工具而不是 Hint，一是这些调优工具比 Hint 更加系统、全面、有效，二是当用了很多 Hint 后，在数据和数据库环境发生变化时，可能带来新的问题，也就是说使用 Hint 可能能带来短期好处，但不会长期持续提高性能。
所以，常规的使用 Hint 的场景应该是：当收集了相关的统计信息后，在不用 hints 使用 explain plan 评估了执行计划以后才应该谨慎使用。对于一些不得不用的在实际环境中，也可能经常面临这样的问题：比如语法不正确或 Hint 使用不当，系统不会给我们任何错误提示。所以很多时候可能也就“稀里糊涂”的、一顿“猛药”下去，"病"好了，哈哈。
随着 Oracle Database 19c 的发布，优化器有一个新的重要的增强：Hint Usage report，它可以报告 hint 的使用情况，该报告包括所有优化器提示的状态，包括 PARALLEL 和 INMEMORY。
Hint Usage report 会显示使用和忽略了哪些提示，并通常解释为什么忽略提示。忽略提示最常见的原因有语法错误（Syntax errors）、不正确的Hint(Unresolved hints)、冲突(Conflicting hints)、受影响的hint(Hints affected by transformations）等，我这里通过dbms_xplan来快速体验一下。
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');
dbms_xplain.display_cursor大家应该经常用，不再多少。19c中新增加的flag:
+HINT_REPORT_USED 显示使用的hints
+HINT_REPORT_UNUSED 显示未使用和语法错误的hint, 缺省format=TYPICAL包含该flag，也就是会报告无效的Hint -- Invalid Hint
+HINT_REPORT 包含上面2个flag的内容，当format=ALL时自动包含该flag.
下面简单看个示例：
PDB1@ZRP>set feedback on sql_id
PDB1@ZRP>select /*+ full(test) index(nonexists) nonfunc(test) */ object_name from test where object_id=123;
OBJECT_NAME
-----------------------------------------------------------------------------------------------------------
OPQTYPE$
1 row selected.
SQL_ID: aqh25km72pgj3
PDB1@ZRP>PDB1@ZRP>select * from dbms_xplan.display_cursor(sql_id=>'aqh25km72pgj3');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID aqh25km72pgj3, child number 0
-------------------------------------
select /*+ full(test) index(nonexists) nonfunc(test) */ object_name
from test where object_id=123
Plan hash value: 1357081020
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
N - index(nonexists)
E - nonfunc
PDB1@ZRP>
说明：最后一部分是Hint Report，它告诉我们对于执行计划Id=1这行，查询块SEL$1有2个未使用的提示：1个是语法错误(E)，因为nonfunc不是一个提示。另一个是index()，它虽然是一种有效的语法，但是它提到了一个不在查询中的别名（nonexists)，然后错误被解析(N)。
下面再看一个忽略正常提示的Hint Report示例：
PDB1@ZRP>alter session set optimizer_ignore_hints=true;
Session altered.
PDB1@ZRP>select /*+ full(test) */ object_name from test where object_id=123;
OBJECT_NAME
----------------------------------------------------------------------------
OPQTYPE$
1 row selected.
SQL_ID: 1azqdh1xrf33w
PDB1@ZRP>select * from dbms_xplan.display_cursor(sql_id=>'1azqdh1xrf33w');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 1azqdh1xrf33w, child number 0
-------------------------------------
select /*+ full(test) */ object_name from test where object_id=123
Plan hash value: 159453698
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
U - full(test) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
PDB1@ZRP>
说明：最后一部分是Hint Report告诉我们有1个未使用的提示，是因为IGNORE_OPTIM_EMBEDDED_HINTS设置被拒绝了
更多详细介绍，请大家参考SQL Tuning Guide（点击“阅读原文”）
原创：老张拉呱
资源下载
关注公众号：数据和云（OraNews）回复关键字获取
2018DTCC , 数据库大会PPT
2018DTC，2018 DTC 大会 PPT
DBALIFE ，“DBA 的一天”海报
DBA04 ，DBA 手记4 电子书
122ARCH ，Oracle 12.2体系结构图
2018OOW ，Oracle OpenWorld 资料
云和恩墨Bethune Pro企业版，集监控，巡检，安全于一身，你的专属数据库实时监控和智能巡检平台，漂亮的不像实力派，你值得拥有！